package rocket.server;

import java.sql.*;

import rocket.logs.Write;
import rocket.logs.WriteType;

/**
 * @author kyle this class is not accessible by general users, just used for
 *         setting up some initial tables.
 *       
 *         if someone manages to hack it, we'll see it in the logs.
 *         -justin
 * 
 * @version
 */
public class Setup {

	private static String user = "bill";

	private static String pass = "bill";
	
	private static String server = "jdbc:mysql://localhost:3306/rocket";
	
	private static String userTableSetup = "CREATE TABLE user ("
			+ "id INT(10) PRIMARY KEY AUTO_INCREMENT,"
			+ "login_attempt INT(1) DEFAULT 0,"
			+ "access ENUM(\"trainer\",\"nursejoy\",\"professor\",\"bill\") DEFAULT 'trainer',"
			+ "name CHAR(50),"
			+ "birth_date DATE,"
			+ "gender CHAR(1),"
			+ "phone CHAR(10),"
			+ "city CHAR(50),"
			+ "region CHAR(50),"
			+ "pharmacy CHAR(50),"
			+ "insurance CHAR(50),"
			+ "ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
			+ ") AUTO_INCREMENT = 1000;";
	
	private static String treatmentTableSetup = "CREATE TABLE treatment ("
			+ "id INT(10) PRIMARY KEY AUTO_INCREMENT,"
			+ "user_id INT(10),"
			+ "prof_id INT(10),"
			+ "nurse_id INT(10),"
			+ "visit_id INT(10),"
			+ "pokemon_id INT(10),"
			+ "treatment_time DATETIME,"
			+ "prof_orders VARCHAR(300),"
			+ "symptoms VARCHAR(300),"
			+ "vitals VARCHAR(300),"
			+ "diagnosis VARCHAR(300),"
			+ "ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
			+ ") AUTO_INCREMENT = 1000;";
	
	private static String prof_orderTableSetup = "CREATE TABLE prof_order ("
			+ "id INT(10) PRIMARY KEY AUTO_INCREMENT,"
			+ "treatment_id INT(10),"
			+ "items VARCHAR(300),"
			+ "lab_work VARCHAR(300),"
			+ "other VARCHAR(300),"
			+ "ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
			+ ") AUTO_INCREMENT = 1000;";
	
	private static String pokemonTableSetup = "CREATE TABLE pokemon ("
			+ "id INT(10) PRIMARY KEY AUTO_INCREMENT,"
			+ "user_id INT(10),"
			+ "pokemon_ndex INT(10),"
			+ "nickname CHAR(50),"
			+ "current_hp INT(3),"
			+ "max_hp INT(3),"
			+ "status SET(\"NONE\",\"BRN\",\"FNT\",\"FRZ\",\"PAR\",\"PSN\",\"SLP\"),"
			+ "ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
			+ ") AUTO_INCREMENT = 1000;";
	
	private static String pokemon_typeTableSetup = "CREATE TABLE pokemon_type ("
			+ "id INT(10) PRIMARY KEY AUTO_INCREMENT,"
			+ "ndex INT(3) UNIQUE,"
			+ "name CHAR(50),"
			+ "type SET(\"none\",\"normal\",\"fire\",\"fighting\",\"water\",\"flying\",\"grass\",\"poison\",\"electric\",\"ground\",\"psychic\",\"rock\",\"ice\",\"bug\",\"dragon\",\"ghost\",\"dark\"),"
			+ "icon MEDIUMBLOB,"
			+ "ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
			+ ") AUTO_INCREMENT = 1000;";
	
	private static String itemTableSetup = "CREATE TABLE item ("
			+ "id INT(10) PRIMARY KEY AUTO_INCREMENT,"
			+ "name CHAR(50) UNIQUE,"
			+ "type ENUM(\"general\",\"medicine\",\"tm\",\"hm\",\"pokeball\",\"key\",\"held\",\"berry\"),"
			+ "cost INT(10),"
			+ "ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
			+ ") AUTO_INCREMENT = 1000;";
	
	private static String visitTableSetup = "CREATE TABLE visit ("
			+ "id INT(10) PRIMARY KEY AUTO_INCREMENT,"
			+ "user_id INT(10),"
			+ "prof_id INT(10),"
			+ "nurse_id INT(10),"
			+ "treatment_id INT(10),"
			+ "visit_time DATETIME,"
			+ "visit_location CHAR(50),"
			+ "ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
			+ ") AUTO_INCREMENT = 1000;";
	
	private static String createBill1 = "CREATE USER 'bill'@'localhost' IDENTIFIED BY 'bill';";
	
	private static String createBill2 = "CREATE USER 'bill'@'%' IDENTIFIED BY 'bill';";
	
	private static String createBill3 = "GRANT ALL ON rocket.* TO 'bill'@'localhost';";
	
	private static String createBill4 = "GRANT ALL ON rocket.* TO 'bill'@'%';";
	
	private static String createBill5 = "GRANT GRANT OPTION ON rocket.* TO 'bill'@'localhost';";
	
	private static String createBill6 = "GRANT GRANT OPTION ON rocket.* TO 'bill'@'%';";
	
	private static String createView1 = "CREATE USER 'view'@'localhost' IDENTIFIED BY 'view';";
	
	private static String createView2 = "CREATE USER 'view'@'%' IDENTIFIED BY 'view';";
	
	private static String createView3 = "GRANT SELECT, UPDATE ON rocket.user TO 'view'@'localhost';";
	
	private static String createView4 = "GRANT SELECT, UPDATE ON rocket.user TO 'view'@'%';";

	public static void main(String[] args) {
		Connection con = null;
		PreparedStatement prepStatement = null;
		//createDefaultUsers();
		// creates bill and view
		// createDefaultUsers();
		
		try {
			con = DriverManager.getConnection(server, user, pass);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		
		writeLog("Database Setup");
		try {
			prepStatement = con.prepareStatement(userTableSetup);
			prepStatement.execute();
			System.out.println("Table 'user' created");
			writeLog("Table 'user' created");
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			writeLog(e.getMessage());
		}

		try {
			prepStatement = con.prepareStatement(treatmentTableSetup);
			prepStatement.execute();
			System.out.println("Table 'treatment' created");
			writeLog("Table 'treatment' created");
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			writeLog(e.getMessage());
		}

		try {
			prepStatement = con.prepareStatement(prof_orderTableSetup);
			prepStatement.execute();
			System.out.println("Table 'prof_order' created");
			writeLog("Table 'prof_order' created");
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			writeLog(e.getMessage());
		}

		try {
			prepStatement = con.prepareStatement(pokemonTableSetup);
			prepStatement.execute();
			System.out.println("Table 'pokemon' created");
			writeLog("Table 'pokemon' created");
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			writeLog(e.getMessage());
		}

		try {
			prepStatement = con.prepareStatement(pokemon_typeTableSetup);
			prepStatement.execute();
			System.out.println("Table 'pokemon' created");
			writeLog("Table 'pokemon_type' created");
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			writeLog(e.getMessage());
		}

		try {
			prepStatement = con.prepareStatement(itemTableSetup);
			prepStatement.execute();
			System.out.println("Table 'item' created");
			writeLog("Table 'item' created");
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			writeLog(e.getMessage());
		}

		try {
			prepStatement = con.prepareStatement(visitTableSetup);
			prepStatement.execute();
			System.out.println("Table 'visit' created");
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			writeLog(e.getMessage());
		}
	}
	
	/**
	 * create default users
	 */
	public static void createDefaultUsers() {
		Connection con = null;
		PreparedStatement prepStatement = null;
		
		try {
			con = DriverManager.getConnection(server, "root", "admin");
		} catch (SQLException e) {
			e.printStackTrace();
			writeLog(e.getMessage());
		}
		
		try {
			if (con != null){
				prepStatement = con.prepareStatement(createBill1);
				prepStatement.execute();
				prepStatement = con.prepareStatement(createBill2);
				prepStatement.execute();
				prepStatement = con.prepareStatement(createBill3);
				prepStatement.execute();
				prepStatement = con.prepareStatement(createBill4);
				prepStatement.execute();
				prepStatement = con.prepareStatement(createBill5);
				prepStatement.execute();
				prepStatement = con.prepareStatement(createBill6);
				prepStatement.execute();
				System.out.println("User 'bill' created");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			writeLog(e.getMessage());
		}
		
		try {
			//p = con.prepareStatement(createView1);
			//p.execute();
			if (con != null){
				prepStatement = con.prepareStatement(createView2);
				prepStatement.execute();
				prepStatement = con.prepareStatement(createView3);
				prepStatement.execute();
				prepStatement = con.prepareStatement(createView4);
				prepStatement.execute();
				System.out.println("User 'view' created");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			writeLog(e.getMessage());
		}
	}
	
	/**
	 * @param s
	 */
	public static void writeLog(String s){
		new Write(WriteType.SERVER,s);
	}
	
	public String toString(){
		return "setup";
	}
}
